﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.IO;
using System.Xml.Serialization;
using System.Xml.Linq;

namespace IPickImporterUI
{
    public class DataLayer
    {
        //connection string
        public readonly string appConnStr = ConfigurationManager.ConnectionStrings["IPickConnStr"].ConnectionString;

        #region "OldCode"
        //public string BulkImportData()
        //{
        //    string result = "";
        //    try
        //    {
        //        NetFlixCatalog.NetflixCatalog cat = new NetFlixCatalog.NetflixCatalog(new Uri("http://odata.netflix.com/Catalog"));
        //        var netFlixTitles = cat.Titles;

        //        //get the catalogs
        //        Catalogs oCatalog = new Catalogs();

        //        //save them

        //        //http://odata.netflix.com/Catalog/Titles?$filter=substringof('',Name)&$format=json [XML]

        //        //XmlSerializer oSerializer = new XmlSerializer(typeof(DataSet));


        //        //string filePath = "D:\\Development\\Mezanmi\\IPickMovies\\Results.xml";
        //        //string filePath = "D:\\Development\\Mezanmi\\IPickMovies\\netflix_structure_file4.xml";
        //        //string schemaPath = "D:\\Development\\Mezanmi\\IPickMovies\\OData.xsd";

        //        /* XmlDocument xSourceRawXML = new XmlDocument();
        //         xSourceRawXML.Load(filePath);
        //         NetflixCatalogExplorer.NetflixParser parser = new NetflixCatalogExplorer.NetflixParser();
        //         List<CatalogTitle> TitleList = parser.ParseSearchResults(xSourceRawXML);
        //         */

        //        /* The Dataset way */
        //        /* string tableName = "NETFlixData";
        //         DataSet dS = new DataSet();
        //         dS.ReadXmlSchema(schemaPath);
        //         dS.ReadXml(filePath);
        //         * 
        //          DataTable dt = dS.Tables[0];
        //         for (int i = 0; i < dt.Rows.Count; i++)
        //         {
        //             DataRow dr = dt.Rows[i];
        //         }
        //         SqlBulkCopy mSqlBulkCopy = new SqlBulkCopy(connStr);
        //         mSqlBulkCopy.DestinationTableName = tableName;
        //         mSqlBulkCopy.WriteToServer(dt);
        //         mSqlBulkCopy.Close();
        //         */

        //        ///* The XDoc way */
        //        /*XDocument doc = XDocument.Load(filePath);
               
                
        //         var elements = doc.Root.DescendantsAndSelf().Elements().Where(d => d.Name.LocalName == "entry");    //all the entry
               

        //         DataSet dsElement = new DataSet();
        //         dsElement.ReadXml(doc.CreateReader());
        //         //dsElement.ReadXml( elements.First().ToString());
        //        */

        //    }
        //    catch (Exception e)
        //    {
        //        result = e.Source + " - " + e.Message;
        //    }

        //    return result;

        //}//end function BulkImportData

        #endregion
      
        /// <summary>
        /// Removes any previous data from the Temporary/Staging Table
        /// </summary>
        /// <returns></returns>
        public string ClearTemporaryTable()
        {
            string result = "";
            try
            {

                using (SqlConnection oConn = new SqlConnection(appConnStr))
                {
                    string spName = "[userIpick].[CleanCatalogs_Staging]";
                    SqlCommand oCommand = new SqlCommand(spName, oConn);

                    oCommand.CommandType = CommandType.StoredProcedure;
                    oConn.Open();

                    using (SqlDataReader reader = oCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToString(reader["ErrorMessage"]);
                        }
                    } //end datareader
                } //end connection
            }
            catch (Exception e)
            {
                result = e.Source + " - " + e.Message;
            }

            return result;
        }


        /// <summary>
        /// Import Data in Bulk to Our SQL Server
        /// </summary>
        /// <returns></returns>
        public string BulkImportData(DataTable dtMovieCatalog)
        {

            string result = "";
            try
            {

                //transform it into a Generic Data Reader Object and Bulk Insert it
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(appConnStr))
                {
                    bulkCopy.BatchSize = 1000;
                    bulkCopy.BulkCopyTimeout = 3600;

                    bulkCopy.DestinationTableName = "Catalogs_Staging";

                    bulkCopy.ColumnMappings.Add("ProviderCatalogId", "ProviderCatalogId");
                    bulkCopy.ColumnMappings.Add("Title", "Title");
                    bulkCopy.ColumnMappings.Add("PosterSmall", "PosterSmall");
                    bulkCopy.ColumnMappings.Add("PosterMedium", "PosterMedium");
                    bulkCopy.ColumnMappings.Add("PosterLarge", "PosterLarge");
                    bulkCopy.ColumnMappings.Add("ReleaseYear", "ReleaseYear");
                    bulkCopy.ColumnMappings.Add("MPAA_Rating", "MPAA_Rating");
                    bulkCopy.ColumnMappings.Add("Description", "Description");
                    bulkCopy.ColumnMappings.Add("ViewerRating", "ViewerRating");
                    bulkCopy.WriteToServer(dtMovieCatalog);

                }
            }
            catch (Exception e)
            {
                result = e.Source + " - " + e.Message;
            }

            return result;

        } //end function BulkImportData


        /// <summary>
        /// Synchs the Imported data from the Staging Table to the final table
        /// </summary>
        /// <returns></returns>
        public string SynchCatalogTables()
        {
            string result = "";
            try
            {

                using (SqlConnection oConn = new SqlConnection(appConnStr))
                {
                    string spName = "[userIpick].[SynchCatalogTable_FromStaging]";
                    SqlCommand oCommand = new SqlCommand(spName, oConn);

                    oCommand.CommandType = CommandType.StoredProcedure;
                    oConn.Open();

                    using (SqlDataReader reader = oCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToString(reader["ErrorMessage"]);
                        }
                    } //end datareader
                } //end connection
            }
            catch (Exception e)
            {
                result = e.Source + " - " + e.Message;
            }

            return result;
        }
       


        /// <summary>
        /// Updates the table to keep track of current marks and each step
        /// </summary>
        /// <param name="catalogs_ImportID">returns new or updated catalog ID</param>
        /// <param name="currentMark"></param>
        /// <returns></returns>
        public string UpdateImportStatus(ref Int64 catalogs_ImportID, string currentMark="", Boolean lastBatch=false)
        {
            string result="";
            try
                {

                    using (SqlConnection oConn = new SqlConnection(appConnStr))
                    {
                        string spName = "[userIpick].[UpdateCatalogs_Import]";
                        SqlCommand oCommand = new SqlCommand(spName,oConn);

                        oCommand.CommandType = CommandType.StoredProcedure;
                        oCommand.Parameters.AddWithValue("@Catalogs_ImportID", catalogs_ImportID);
                        oCommand.Parameters.AddWithValue("@CurrentMark", currentMark);
                        oCommand.Parameters.AddWithValue("@LastBatch", lastBatch);

                        oConn.Open();

                        using (SqlDataReader reader = oCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                catalogs_ImportID = Convert.ToInt64(reader["catalogs_ImportID"]);
                                result = Convert.ToString(reader["ErrorMessage"]);
                            }
                        } //end datareader
                   } //end connection
                }
            catch (Exception e)
            {
                result = e.Source + " - " + e.Message;
            }

            return result;
            


 

        }
    }   //end class DataLayer
} //end nameSpace


